<?php
namespace core\service;
// +----------------------------------------------------------------------
// | 数据库管理
// +----------------------------------------------------------------------
// | Copyright (c) 2006~2017 Even Yin All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: Even yin <416467069@qq.com> <13022156261>
// +----------------------------------------------------------------------
use \ZipArchive;
use \DirectoryIterator;
use \Exception;
use think\Db;
use think\Cache;
use core\service\BaseService as BaseService;
class Database extends BaseService
{

  protected $lock;                      //执行lock锁机制
  protected $size;                      //单卷大小，单位Mb
  protected $path;                      //备份根目录
  protected $backup_path;               //备份存储目录

  protected $table_list;                //备份表名集合
  protected $table_total;               //备份表总数

  protected $now_table_index = 0;       //当前备份表INDEX
  protected $now_table_exec_count = 0;  //当前备份表已执行次数
  protected $now_table_total = 0;       //当前备份表数据总条数
  protected $filename = '';             //当前备份表存储SQL文件名

  protected $sql_files_arr;
  protected $now_file_index = 0;
  protected $next_file_index = 0;

  protected $total_limit = 200;         //单次备份最大数据量200条

  /* -----------------------------------START------------------------------- */
  /*
  * 构造函数
  */
  public function __construct(){
    $this->size     = 2;
    $this->path  = dirname(__FILE__) . '/../database';
    $this->backup_path = $this->path.'/'.date('YmdHis');
    $this->lock  = empty(Cache::get('DATABASE_BACKUP_LOCK'))?false:true;
    $this->lock  = empty(Cache::get('DATABASE_RECOVERY_LOCK'))?false:true;
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 查询备份数据
  */
  public function pageQuery($page=1,$page_size=20){
    $total_count = 0;
    $list = [];
    $files = scandir($this->path);
    $total_count = count($files);
    foreach ($files as $k => $file) {
       if(count($list)==$page_size){
         break;
       }
       if($k>=($page-1)*$page_size){
         if($file == '.' || $file == '..') continue;
         if(is_dir($this->path.'/'.$file)){
           $list[] = [
             'name' => $file,
             'size' => $this->getRealSize($this->getDirSize($this->path.'/'.$file)),
             'time' => date('Y-m-d H:i:s',strtotime($file))
           ];
         }
       }
    }
    return $data = [
        'total_count' => $total_count-2,
        'data' => $list,
        'page' => $page
    ];
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 备份数据
  */
  public function backup(){
    //锁机制
    if($this->lock === true){
      $this->error = '正在执行备份操作...';
      return false;
    }
    //锁定备份
    Cache::set('DATABASE_BACKUP_LOCK',1);
    $this->lock = true;
    //创建目录
    if (!is_dir($this->backup_path)){
      mkdir($this->backup_path, 0777);
    }
    //备份数据
		$result = [];
		do{
		  	$result = $this->execBackUp();
		} while (
			  $result['totalpercentage'] < 100
		);
    //备份完成进行解锁
    Cache::set('DATABASE_BACKUP_LOCK',null);
    $this->lock = false;
    return $result;
	}
  /* -----------------------------------END------------------------------- */
  /*
  * 恢复数据
  */
  public function recovery($name=''){
    //锁机制
    if($this->lock === true){
      $this->error = '正在执行操作...';
      return false;
    }
    //判断文件夹是否存在
    if(empty($name) || !is_dir($this->path.'/'.$name)){
      $this->error = 'filename is not exists!';
      return false;
    }
    //锁定恢复数据操作
    Cache::set('DATABASE_RECOVERY_LOCK',1);
    $this->lock = true;
    //执行恢复数据操作
		$result = [];
		do{
		  	$result = $this->execRecovery($this->path.'/'.$name);
        //如果执行出错,需要提前终止恢复数据
        if($result == false){
          break;
        }
		} while (
			  $result['totalpercentage'] < 100
		);
    //数据恢复完成解锁
    Cache::set('DATABASE_RECOVERY_LOCK',null);
    $this->lock = false;
    return $result;
	}
  /* -----------------------------------END------------------------------- */
  /*
  * 执行恢复操作
  */
  private function execRecovery($path){
    try{
        $filesarr = $this->getSqlFiles($path);
        $totalpercentage = 100;
        $this->now_file_index = $this->next_file_index;
        if (isset($filesarr[$this->now_file_index]))
        {
            $this->importSqlFile($path . DIRECTORY_SEPARATOR . $filesarr[$this->now_file_index]);
            $totalpercentage = $this->now_file_index / count($this->sql_files_arr) * 100;
            $this->next_file_index = $this->now_file_index + 1;
        }
        return [
            'nowfileidex' => $this->now_file_index, //当前正在恢复的文件
            'nextfileidx' => $this->next_file_index,
            'totalpercentage' => (int) $totalpercentage, //总百分比
        ];
    } catch (\Exception $e){
        $this->error = $e->getMessage();
        // throw $e;
        return false;
    }
  }
  public function getSqlFiles($path){
    if(!$this->sql_files_arr){
      $files = [];
      foreach (scandir($path) as $k => $file) {
         if($file == '.' || $file == '..') continue;
         if(is_file($path.'/'.$file)){
           $file_1 = explode('#',$file);
           if(isset($file_1[1])){
             $file_2 = explode('.',$file_1[1]);
             $_tb = $file_1[0];
             $_index = $file_2[0];
             $_ext = $file_2[1];
             if(isset($files[$_tb]) && !is_array($files[$_tb])){
               $files[$_tb] = [];
             }
             $files[$_tb][$_index] =  $file;
           }
         }
      }
      $kfiles = [];
      foreach ($files as $k => $v) {
        ksort($v,1);
        foreach ($v as $vv) {
          $kfiles[] = $vv;
        }
      }
      $this->sql_files_arr = $kfiles;
    }
    return $this->sql_files_arr;
  }

  private function importSqlFile($sqlfile)
  {
      if (is_file($sqlfile))
      {
          try
          {
              $content = file_get_contents($sqlfile);
              $arr = explode(';' . PHP_EOL, $content);
              foreach ($arr as $a)
              {
                  if (trim($a) != '')
                  {
                      Db::query($a);
                  }
              }
          } catch (\Exception $e)
          {
              $this->error = $e->getMessage();
              return false;
          }
      }
      return true;
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 下载备份包
  */
  public function download($name=''){
    if(empty($name) || !is_dir($this->path.'/'.$name)){
      echo 'filename is not exists!';exit;
    }
    $path = $this->toZip($name);
    if(!file_exists($path)){
      echo 'zip file is not exists!';exit;
    }
    header("Cache-Control: public");
    header("Content-Description: File Transfer");
    header('Content-disposition: attachment; filename='.basename($path)); //文件名
    header("Content-Type: application/zip"); //zip格式的
    header("Content-Transfer-Encoding: binary"); //告诉浏览器，这是二进制文件
    header('Content-Length: '. filesize($path)); //告诉浏览器，文件大小
    @readfile($path);
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 根据文件名进行zip打包操作
  */
  public function toZip($name){
    if(is_file($this->path.'/'.$name.'/'.$name.'.zip')) return $this->path.'/'.$name.'/'.$name.'.zip';
    $zip = new \ZipArchive();
    if($zip->open($this->path.'/'.$name.'/'.$name.'.zip', ZipArchive::OVERWRITE)=== TRUE){
      $this->addFileToZip($this->path.'/'.$name.'/', $zip); //调用方法，对要打包的根目录进行操作，并将ZipArchive的对象传递给方法
      $zip->close(); //关闭处理的zip文件
    }
    return $this->path.'/'.$name.'/'.$name.'.zip';
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 添加文件到zip中
  */
  public function addFileToZip($path,$zip){
    $handler=opendir($path); //打开当前文件夹由$path指定。
    while(($filename=readdir($handler))!==false)
    {
        if($filename != "." && $filename != ".."){
            if(is_dir($path."/".$filename)){
                $this->addFileToZip($path."/".$filename, $zip);
            }else{
                //将文件加入zip对象
                $zip->addFromString($filename, file_get_contents($path."/".$filename));
                // $zip->addFile($path."/".$filename);
            }
        }
    }
    @closedir($path);
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 删除数据
  */
  public function delete($name=''){
    $files = explode(',',$name);
    foreach ($files as $file) {
      $fs = scandir($this->path.'/'.$file);
      foreach ($fs as $f) {
        if($f!='.' && $f!='..'){
					if(!is_dir($this->path.'/'.$file.'/'.$f)){
						if(file_exists($this->path.'/'.$file.'/'.$f)) unlink($this->path.'/'.$file.'/'.$f);
					}else{
            rmdir($this->path.'/'.$file.'/'.$f);
          }
				}
      }
      rmdir($this->path.'/'.$file);
    }
    return true;
	}
  /* -----------------------------------END------------------------------- */
  /*
  * 执行备份操作
  */
  public function execBackUp(){
    $totalpercentage = 100;                                       //总完成比例
    $tablepercentage = 100;                                       //总完成比例
    $tablelist = $this->getTableList();                           //获取所有需要备份的表名集合
    $nexttable = $nowtable = '';                                  //初始化下一个表和当前表
    $nexttableidx = $nowtableidx = $this->now_table_index;        //当前表执行的INDEX
    $nextstorefile = $nowstorefile = '';
    if (isset($tablelist[$nowtableidx]))                          //判断是否存在表，不存在说明备份完成啦！
    {
        $nexttable = $nowtable = $tablelist[$nowtableidx];        //获取到当前备份的表名称
        $sqlstr = '';

        if($this->now_table_exec_count == 0){                     //分卷第一次执行
          //Drop 建表
          $sqlstr .= 'DROP TABLE IF EXISTS `' . $nowtable . '`;' . PHP_EOL;
          $res = Db::query('SHOW CREATE TABLE `' . $nowtable . '`');
          $sqlstr .= $res[0]['Create Table'] . ';' . PHP_EOL;
          //开启写入SQL文件中
          file_put_contents($this->backup_path . DIRECTORY_SEPARATOR . $this->getFileName(), file_get_contents($this->backup_path . DIRECTORY_SEPARATOR . $this->getFileName()) . $sqlstr);
        }

        //查询到当前表中有多少数据需要备份
        if($this->now_table_exec_count == 0){
          $this->getTableTotal($nowtable);
        }

        //记录SQL语句
        if($this->now_table_exec_count < $this->now_table_total){
          $this->SingleInsertRecord($nowtable,$this->now_table_exec_count);
        }

        //计算百分比
        $totalpercentage = ($this->now_table_index ) / count($tablelist) * 100;
        if($this->now_table_total != 0){
            $tablepercentage = $this->now_table_exec_count / $this->now_table_total * 100;
        }

        //获取下一个需要记录的SQL名
        $nextstorefile = $nowstorefile = $this->getFileName();
        if ($tablepercentage >= 100)
        {
            $this->now_table_index++;
            $nexttableidx = $this->now_table_index;
            $this->now_table_exec_count = 0;
            if (isset($tablelist[$nexttableidx]))
            {
                $nexttable = $tablelist[$nexttableidx];
                $nextstorefile = $nexttable . '#0.sql';
                $this->setFileName($nextstorefile);//不存在文件则新建。
            }
        }
    }
    return [
        'nowtable' => $nowtable, //当前正在备份的表
        'nowtableidx' => $nowtableidx, //当前正在备份表的索引
        'nowstorefile' => $nowstorefile, //当前备份存储的文件名
        'nowtableexeccount' => $this->now_table_exec_count, //当前表执行条数
        'nowtabletotal' => $this->now_table_total, //当前表执行总条数
        'nexttable' => $nexttable, //下一个要备份的表
        'nexttableidx' => $nexttableidx, //下一个要备份表的索引
        'nextstorefile' => $nextstorefile, //下一个要存储的文件名
        'totalpercentage' => (int) $totalpercentage, //总百分比
        'tablepercentage' => (int) $tablepercentage, //当前表百分比
    ];
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 获取备份SQL文件名称
  */
  public function getFileName()
  {
    //第一次执行
    if(!$this->filename){
      $this->filename = isset($this->table_list[$this->now_table_index]) ? $this->table_list[$this->now_table_index] . '#0.sql' : '';
    }
    //不是文件就要创建文件
    if(!is_file($this->backup_path.'/'.$this->filename)){
        fopen($this->backup_path.'/'.$this->filename,"x+");
    }
    return $this->filename;
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 设置备份SQL文件名称
  */
  public function setFileName($filename){
    $this->filename = $filename;
    if(!is_file($this->backup_path.'/'.$this->filename)){
        fopen($this->backup_path.'/'.$this->filename,"x+");
    }
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 获取表数据量
  */
  public function getTableTotal($table)
  {
      $count = Db::query('select count(*) from ' . $table);
      $this->now_table_total = $count[0]['count(*)'];
      return $this->now_table_total;
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 获取所有表
  */
  public function getTableList(){
      if(!$this->table_list){
        $res = Db::query('show table status');
        foreach ($res as $r){
          $this->table_list[] = $r['Name'];
        }
      }
      return $this->table_list;
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 外部传入参数设置表列表
  */
  public function setTableList($tables=[]){
    $this->table_list = $tables;
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 写入SQL文件中
  */
  public function SingleInsertRecord($tablename, $limit){
      $sql = 'select * from `' . $tablename . '` limit ' . $limit . ',' . $this->total_limit;
      $valueres = Db::query($sql);
      $insertsqlv = '';
      $insertsql = 'insert into `' . $tablename . '` VALUES ';
      foreach ($valueres as $v)
      {
          $insertsqlv .= ' ( ';
          foreach ($v as $_v)
          {
              $insertsqlv .= "'" . $_v . "',";
          }
          $insertsqlv = rtrim($insertsqlv, ',');
          $insertsqlv .= ' ),';
      }
      $insertsql .= rtrim($insertsqlv, ',') . ' ;' . PHP_EOL;
      //SQL语句写入SQL文件中
      $this->checkFileSize();
      file_put_contents($this->backup_path.'/'.$this->getFileName(),file_get_contents($this->backup_path.'/'.$this->getFileName()).$insertsql);
      //计算
      $this->now_table_exec_count += $this->total_limit;
      $this->now_table_exec_count = $this->total_limit >= $this->now_table_total ? $this->now_table_total : $this->now_table_exec_count;
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 获取文件夹Size
  */
  public function getDirSize($dir){
    $size = 0;
    foreach(scandir($dir) as $file){
      if($file == '.' || $file == '..') continue;
      if(is_file($dir.'/'.$file)){
        $size = $size + filesize($dir.'/'.$file);
      }
      if(is_dir($dir.'/'.$file)){
        $size += $this->getDirSize($dir.'/'.$file);
      }
    }
    return $size;
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 格式化size
  */
  function getRealSize($size){
    $kb = 1024;   // Kilobyte
    $mb = 1024 * $kb; // Megabyte
    $gb = 1024 * $mb; // Gigabyte
    $tb = 1024 * $gb; // Terabyte
    if($size < $kb){
      return $size." B";
    }else if($size < $mb){
     return round($size/$kb,2)." KB";
    }else if($size < $gb){
     return round($size/$mb,2)." MB";
    }else if($size < $tb){
     return round($size/$gb,2)." GB";
    }else{
     return round($size/$tb,2)." TB";
    }
  }
  /* -----------------------------------END------------------------------- */
  /*
  * 检查文件大小
  */
  public function checkFileSize()
  {
      clearstatcache();
      //如果文件大于2mb后就创建分卷
      $b = filesize($this->backup_path.'/'.$this->getFileName()) < $this->size * 1024 * 1024 ? true : false;
      if ($b === false){
          $filearr = explode('#',$this->getFileName());
          if (count($filearr) == 2){
              $fileext = explode('.', $filearr[1]); //.sql
              $filename = $filearr[0] . '#' . ((int)$fileext[0] + 1) . '.sql';
              $this->setFileName($filename);
          }
      }
  }
  /* -----------------------------------END------------------------------- */
}
